﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data.Common;

//@author: Lénia Andrade nº 5607


namespace LP_C_Sharp_2011_2012
{
    class Statistics
    {

       
        /*This method returns a list with the establishments availables in a certain year*/

        public List<string> establishments_per_year(int year)
        {
            
            SQLiteConnection connection = new SQLiteConnection("Data Source=Rebides.db");
            connection.Open();
            SQLiteTransaction transaction = connection.BeginTransaction();
            SQLiteCommand command = connection.CreateCommand();
            List<string> databaseData = new List<string>();

          
            command.CommandText = string.Format("SELECT * FROM teachers_information_year_{0}", year);

            DbDataReader reader = command.ExecuteReader();

            while (reader.Read())
               {
                databaseData.Add(reader.GetString(8));
               }

            transaction.Commit();
            connection.Close();

            List<string> establishments_year = new List<string>();
            establishments_year.AddRange(databaseData.Distinct());
            establishments_year.Sort();
            return establishments_year;
        }



        /*This method returns a dictionary that associates the number of teachers
         * with their category (in a specific year)*/

        public Dictionary<string, int> teachers_per_category_per_year(int year) {

            SQLiteConnection connection = new SQLiteConnection("Data Source=Rebides.db");
            connection.Open();
            SQLiteTransaction transaction = connection.BeginTransaction();
            SQLiteCommand command = connection.CreateCommand();
            List<Statistic_2> databaseData = new List<Statistic_2>();
            List<string> categories = new List<string>();

            command.CommandText = string.Format("SELECT * FROM teachers_information_year_{0}", year);

            DbDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                databaseData.Add(new Statistic_2 { Year = year, Teachers= ((string)reader[1]),Category = ((string)reader[4]) });
                categories.Add((string)reader[4]);
            }

            transaction.Commit();
            connection.Close();

            Dictionary<string, int> teachers_per_category = new Dictionary<string, int>();

            foreach (string cat in categories.Distinct())
            {
                var c =
                    from data in databaseData
                    where data.Category == cat
                    select data.Teachers;

                    teachers_per_category.Add(cat,c.Distinct().Count());
            }
            return teachers_per_category;
        }



         /*
        This method returns a dictionary that associates the number of teachers 
          * belonging to a certain establishment in a specific year with their category */

        public Dictionary<string, int> teachers_per_category_per_establishment_per_year(int year, string estab)
        {

            SQLiteConnection connection = new SQLiteConnection("Data Source=Rebides.db");
            connection.Open();
            SQLiteTransaction transaction = connection.BeginTransaction();
            SQLiteCommand command = connection.CreateCommand();
            List<Statistic_3> databaseData = new List<Statistic_3>();
            List<string> categories = new List<string>();

            command.CommandText = string.Format("SELECT * FROM teachers_information_year_{0}", year);

            DbDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                databaseData.Add(new Statistic_3 { Year = year, Teachers = ((string)reader[1]), Category = ((string)reader[4]),Establishment=((string)reader[8]) });
            }

            transaction.Commit();
            connection.Close();

            Dictionary<string, int> teachers_per_category_per_establishment = new Dictionary<string, int>();
            
            var c =
                from data in databaseData
                where data.Establishment == estab
                orderby data.Category
                select data.Category;

            foreach (string Result in c.Distinct())
            {
                var f =
                    from data in databaseData
                    where data.Category == Result && data.Establishment == estab
                    select data.Teachers;

                teachers_per_category_per_establishment.Add(Result, f.Count());            
            }
     
            return teachers_per_category_per_establishment;
        }
    }


    /*
     * Class that creates list<T> relative to the second statistic (teachers per category and per year)*/

    public class Statistic_2
    {
        public int Year { get; set; }
        public string Teachers { get; set; }
        public string Category{ get; set; }
    }


    /*
    * Class that creates list<T> relative to the third statistic (teachers per category per establishment and per year)*/

    public class Statistic_3
    {
        public int Year { get; set; }
        public string Teachers { get; set; }
        public string Category { get; set; }
        public string Establishment { get; set; }
    }
}
